<?php

/**
 * This is the model class for table "zy_survey_user".
 *
 * The followings are the available columns in table 'zy_survey_user':
 * @property integer $suid
 * @property integer $partner_id
 * @property string $activety_code
 * @property string $first_name
 * @property string $last_name
 * @property string $email
 * @property string $zipcode
 * @property integer $job
 * @property string $mobile
 * @property string $favo
 * @property string $create_time
 * @property string $update_time
 */
class SurveyUser extends CActiveRecord {
	/**
	 * Returns the static model of the specified AR class.
	 * 
	 * @param string $className
	 *        	active record class name.
	 * @return SurveyUser the static model class
	 */
	public static function model($className = __CLASS__) {
		return parent::model ( $className );
	}
	
	/**
	 *
	 * @return string the associated database table name
	 */
	public function tableName() {
		return 'zy_survey_user';
	}
	
	/**
	 *
	 * @return array validation rules for model attributes.
	 */
	public function rules() {
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array (
				array (
						'activety_code,partner_id,first_name, last_name, email,mobile,Identity_id',
						'required' 
				),
				array (
						'partner_id, job,award_address_id',
						'numerical',
						'integerOnly' => true 
				),
				array (
						'first_name, last_name, email, zipcode, favo,Identity_id,id_number,IP,lang',
						'length',
						'max' => 50 
				),
				
				array (
						'mobile',
						'length',
						'max' => 13 
				),
				
				array (
						'winning_status,sms_status,id_type',
						'length',
						'max' => 1 
				),
				array (
						'suburb,state',
						'length',
						'max' => 150 
				),
				array (
						'full_street_address',
						'length',
						'max' => 300 
				),
				
				// The following rule is used by search().
				// Please remove those attributes that should not be searched.
				array (
						'suid, partner_id,birthday,full_street_address,suburb,state,id_type,id_number, activety_code,winning_status,Identity_id,sms_status, first_name, last_name, email, zipcode, job, mobile, favo, create_time, update_time',
						'safe',
						'on' => 'search' 
				) 
		)
		;
	}
	
	/**
	 *
	 * @return array relational rules.
	 */
	public function relations() {
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array ();
	}
	
	/**
	 *
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels() {
		return array (
				'suid' => Yii::t ( 'base', 'Suid' ),
				'partner_id' => Yii::t ( 'base', 'Partners' ),
				'activety_code' => Yii::t ( 'base', 'Title' ),
				'first_name' => Yii::t ( 'base', 'First Name' ),
				'last_name' => Yii::t ( 'base', 'Last Name' ),
				'email' => Yii::t ( 'base', 'email' ),
				'zipcode' => Yii::t ( 'base', 'Zipcode' ),
				'job' => Yii::t ( 'base', 'Job' ),
				'mobile' => Yii::t ( 'base', 'Mobile' ),
				'favo' => Yii::t ( 'base', 'Favo' ),
				'Identity_id' => Yii::t ( 'base', 'Identity ID' ),
				'award_address_id' => Yii::t ( 'base', 'Award Address' ),
				'winning_status' => Yii::t ( 'base', 'Winning Status' ),
				'sms_status' => Yii::t ( 'base', 'SMS Status' ),
				'birthday' => Yii::t ( 'base', 'Birthday' ),
				'full_street_address' => Yii::t ( 'base', 'Full Street Address' ),
				'suburb' => Yii::t ( 'base', 'Suburb' ),
				'state' => Yii::t ( 'base', 'State' ),
				'id_type' => Yii::t ( 'base', 'ID Type' ),
				'id_number' => Yii::t ( 'base', 'ID Number' ),
				'ip' => Yii::t ( 'base', 'IP' ),
				'lang' => Yii::t ( 'base', 'lang' ),
				'create_time' => 'Create Time',
				'update_time' => 'Update Time' 
		);
	}
	
	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 * 
	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
	 */
	public function search() {
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.
		$level = yii::app ()->user->level ();
		$user_id = yii::app ()->user->id;
		$criteria = new CDbCriteria ();
		
		$criteria->compare ( 'suid', $this->suid );
		
		if ($level == 9) {
			$criteria->compare ( 'partner_id', $user_id );
		} else {
			$criteria->compare ( 'partner_id', $this->partner_id );
		}
		$criteria->compare ( 'activety_code', $this->activety_code, true );
		$criteria->compare ( 'first_name', $this->first_name, true );
		$criteria->compare ( 'last_name', $this->last_name, true );
		$criteria->compare ( 'email', $this->email, true );
		$criteria->compare ( 'zipcode', $this->zipcode, true );
		$criteria->compare ( 'job', $this->job );
		$criteria->compare ( 'mobile', $this->mobile, true );
		$criteria->compare ( 'favo', $this->favo, true );
		$criteria->compare ( 'Identity_id', $this->Identity_id, true );
		$criteria->compare ( 'winning_status', $this->winning_status, true );
		$criteria->compare ( 'sms_status', $this->sms_status, true );
		$criteria->compare ( 'birthday', $this->birthday, true );
		$criteria->compare ( 'full_street_address', $this->full_street_address, true );
		$criteria->compare ( 'suburb', $this->suburb, true );
		$criteria->compare ( 'state', $this->state, true );
		$criteria->compare ( 'id_type', $this->id_type, true );
		$criteria->compare ( 'id_number', $this->id_number, true );
		$criteria->compare ( 'IP', $this->IP, true );
		$criteria->compare ( 'lang', $this->lang, true );
		$criteria->compare ( 'create_time', $this->create_time, true );
		$criteria->compare ( 'update_time', $this->update_time, true );
		
		return new CActiveDataProvider ( $this, array (
				'criteria' => $criteria,
				'sort' => array (
						'defaultOrder' => 'create_time desc' 
				) 
		) );
	}
	
	// 根据活动代码查询标题
	public function getActivetyCodeTitle() {
		$level = yii::app ()->user->level ();
		$user_id = yii::app ()->user->id;
		if ($level == 1) {
			$sql = "select * from zy_activity";
		} else {
			$sql = "select * from zy_activity where user_id='" . $user_id . "'";
		}
		$rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		return CHtml::listData ( $rs, 'activity_code', 'title' );
	}
	// 根据code获取此活动中的设置礼品数
	public function getGiftNumber($activity_code, $user_id) {
		$sql = "select mobile from zy_survey_user where partner_id='" . $user_id . "' and activety_code='" . $activity_code . "' and winning_status = 0";
		$rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		shuffle ( $rs ); // 打乱数组
		return $rs;
	}
	// 调查用户,取得ID
	public function getSuid($moblie, $user_id) {
		$sql = "select suid from zy_survey_user where partner_id='" . $user_id . "' and mobile='" . $moblie . "'";
		$rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		return $rs [0] ['suid'];
	}
	// 更新已中奖用户记录的中奖字段
	public function setLotteryUserStatus($moblie, $user_id,$activity_code) {
		$sql = "update zy_survey_user set `winning_status` = 1  where partner_id='" . $user_id . "' and mobile='" . $moblie . "' and activety_code='".$activity_code."'";
		$rs = yii::app ()->db->createCommand ( $sql )->execute ();
		return $rs;
	}
	
	// 更新短信发送成功用户记录的短信字段
	public function setSmsUserStatus($moblie, $user_id) {
		$sql = "update zy_survey_user set `sms_status` = 1 where partner_id='" . $user_id . "' and mobile='" . $moblie . "'";
		$rs = yii::app ()->db->createCommand ( $sql )->execute ();
		return $rs;
	}
	
	// 更新短信发送成功用户记录的短信字段
	public function setSmsUserStatusId($id) {
		$sql = "update zy_survey_user set `sms_status` = 1 where suid='" . $id . "'";
		$rs = yii::app ()->db->createCommand ( $sql )->execute ();
		return $rs;
	}
	
	// 切换中奖状态
	public function switchStatus($num) {
		if ($num != '') {
			if ($num == 1) {
				
				return Yii::t ( 'base', 'Have been winners' );
			} else {
				return Yii::t ( 'base', 'No winners' );
			}
		}
	}
	
	// 切换短信发送状态
	public function SmsSwitchStatus($num) {
		if ($num != '') {
			if ($num == 1) { // 发送成功
				return Yii::t ( 'base', 'Sent successfully' );
			} elseif ($num == 0) { // 未发送
				return Yii::t ( 'base', 'Unsent' );
			} else { // 发送失败
				return Yii::t ( 'base', 'Failed to send' );
			}
		}
	}
	
	// 随机从zy_gift_password表中拿出一个密码
	public function getPassword($activety_code, $user_id) {
		$sql = "select activity_id from zy_activity where user_id='" . $user_id . "' and activity_code='" . $activety_code . "'";
		$rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		$sqls = "select gift_password_id,password from zy_gift_password where activity_id='" . $rs [0] ['activity_id'] . "' and suser_id='" . $user_id . "' and state= 0";
		$rss = Yii::app ()->db->createCommand ( $sqls )->queryAll ();
		array_unique ( $rss );
		$result = array_rand ( $rss, 1 );
		return $rss [$result];
	}
	
	// 改变zy_gift_password 中 拿出密码记录的状态
	public function setPasswordState($gift_password_id) {
		$sql = "update zy_gift_password set state = 1 where gift_password_id='" . $gift_password_id . "'";
		yii::app ()->db->createCommand ( $sql )->execute ();
	}
	
	//通过密码查询密码记录ID
	public function setSuserId($gift_password_id,$suid){
		$sql = "update zy_gift_password set suser_id = ".$suid." where gift_password_id='" . $gift_password_id . "'";
		yii::app ()->db->createCommand ( $sql )->execute ();
	}
	
	// 获取活动过期时间急地址
	public function getActivityAndAddress($activety_code, $user_id) {
		$sql = "select activity_out_time,address1,address2,address3 from zy_activity where user_id='" . $user_id . "' and activity_code='" . $activety_code . "'";
		$rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		return $rs [0];
	}
	
	// 获取详细获奖地址
	public function getDetailsAwardAddress($address_id, $userId) {
		$sql = "select * from zy_award_address where address_id='" . $address_id . "' and  belong = '" . $userId . "'";
		$rs = yii::app ()->db->createCommand ( $sql )->queryAll ();
		return $rs [0] ['address'];
	}
	/*
	 * //发送邮件给admin public function sendMail($models,$password){ // multiple recipients $to = 'kingarthurtoo@gmail.com'; // note the comma // subject $subject = 'Winning Staff Notice'; // message $startTable = ' <html> <head> <title>Winning Staff Notice</title> </head> <body> <p>Here are the birthdays upcoming in August!</p> <table> <tr> <th>'.Yii::t('base', 'First Name').'</th> <th>'.Yii::t('base', 'Last Name').'</th> <th>'.Yii::t('base', 'email').'</th> <th>'.Yii::t('base', 'Mobile').'</th> <th>'.Yii::t('base', 'Identity ID').'</th> <th>'.Yii::t('base', 'Winning Status').'</th> <th>'.Yii::t('base', 'SMS Status').'</th> <th>'.Yii::t('base', 'Password').'</th> </tr> '; for($i=0; $i<count($models); $i++){ $tr =' <tr> <td>'.$models[$i]->first_name.'</td> <td>'.$models[$i]->last_name.'</td> <td>'.$models[$i]->email.'</td> <td>'.$models[$i]->mobile.'</td> <td>'.$models[$i]->Identity_id.'</td> <td>'.$models[$i]->winning_status.'</td> <td>'.$models[$i]->sms_status.'</td> <td>'.$password[$i].'</td> </tr> '; } $Endtable =' </table> </body> </html> '; $message = $startTable.$tr.$Endtable; // To send HTML mail, the Content-type header must be set $headers = "MIME-Version: 1.0\r\n"; $headers .= "Content-type: text/html; charset=utf-8"; // Mail it mail($to, $subject, $message, $headers); }
	 */
	function sendEmail($email_to, $first_name, $url, $lang) {
		// $currentLang= Yii::app()->language;
		if ($lang == "en") {
			$email_message = file_get_contents ( "http://cp.netcube.com.au/newsletteren.html" );
		} else {
			$email_message = file_get_contents ( "http://cp.netcube.com.au/newsletter.html" );
		}
		$email_message = str_replace ( "{first_name}", $first_name, $email_message );
		$email_message = str_replace ( "{url}", $url, $email_message );
		// $email_subject = Yii::t('base', 'Novi telecommunications activities confirmation code (via Novi telecommunications system (the default) to send)');
		if ($lang == "en") {
			$email_subject = 'Netcube - Please confirm your registration for our 1GB Free Broadband Data!';
		} else {
			$email_subject = 'Netcube - 请确认注册领取您的1GB免费无线上网流量。';
		}
		
		$headers = 'Content-type: text/html; charset=utf-8' . "\r\n";
		$headers .= 'From: Netcube<sales@netcube.com.au>' . "\r\n";
		$headers .= "Reply-To: " . strip_tags ( 'sales@netcube.com.au' ) . "\r\n";
		$headers .= 'X-Mailer: PHP/' . phpversion () . "\r\n";
		$i = @mail ( $email_to, $email_subject, $email_message, $headers );
	}
	//0表示中奖，1表示没中奖
	function winningStatus($winningstatus)
	{
		if($winningstatus==0)
		{
			$winningstatusname="没中奖";
		}elseif($winningstatus==1){
			$winningstatusname="中奖";
		}
		return $winningstatusname;
	}
	
	/**
	 * 获取电话表中人员.
	 */
	public function getSurveyUser($code){
		//加载PHPexcel类文件
		Yii::$enableIncludePath = false;
		Yii::import('application.extensions.PHPExcel', 1);
		//获取活动标题
		$activityName = SurveyUser::model()->getActivityName($code);
		$sql="SELECT * FROM zy_survey_user WHERE activety_code='".$code."'";
		$rs = yii::app()->db->createCommand($sql)->queryAll();
	
		$objectPHPExcel = new PHPExcel();
		$objectPHPExcel->setActiveSheetIndex(0);
		//设置表头格式
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFill()->getStartColor()->setARGB('FFCCCCCC');
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFont()->setName('Candara');
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFont()->setSize(12);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFont()->setBold(true);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFont()->setBold(true);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFont()->setBold(true);
		//报表头的输出
		$objectPHPExcel->getActiveSheet()->setCellValue('A1','Activity');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
		$objectPHPExcel->getActiveSheet()->setCellValue('B1','First Name');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('C1','Last Name');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('D1','Email');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
		$objectPHPExcel->getActiveSheet()->setCellValue('E1','Zipcode');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$objectPHPExcel->getActiveSheet()->setCellValue('F1','Job');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
		$objectPHPExcel->getActiveSheet()->setCellValue('G1','Mobile');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(60);
		$objectPHPExcel->getActiveSheet()->setCellValue('H1','Savor');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('I1','Winning Status');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('J1','Sms Status');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('K1','Identity Id');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('L1','Birthday');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('M1','Full Street Address');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('N1','Suburb');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('O1','State');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('P1','Type');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('Q1','Id Number');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('R1','IP');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25);
		
		for ($i=0; $i < count($rs); $i++){
			$winningStatus = SurveyUser::model()->getReplaceAttribute($rs[$i][winning_status]);
			$smsStatus = SurveyUser::model()->getSmsReplaceAttribute($rs[$i][sms_status]);
			$idType = SurveyUser::model()->getTypeReplaceAttribute($rs[$i][id_type]);
			
			$objectPHPExcel->getActiveSheet()->setCellValue('A'.($i+2),$activityName);
			$objectPHPExcel->getActiveSheet()->setCellValue('B'.($i+2),$rs[$i][first_name]);
			$objectPHPExcel->getActiveSheet()->setCellValue('C'.($i+2),$rs[$i][last_name]);
			$objectPHPExcel->getActiveSheet()->setCellValue('D'.($i+2),$rs[$i][email]);
			$objectPHPExcel->getActiveSheet()->setCellValue('E'.($i+2),$rs[$i][zipcode]);
			$objectPHPExcel->getActiveSheet()->setCellValue('F'.($i+2),$rs[$i][job]);
			$objectPHPExcel->getActiveSheet()->setCellValue('G'.($i+2),$rs[$i][mobile].' ');
			$objectPHPExcel->getActiveSheet()->setCellValue('H'.($i+2),$rs[$i][savor]);
			$objectPHPExcel->getActiveSheet()->setCellValue('I'.($i+2),$winningStatus);
			$objectPHPExcel->getActiveSheet()->setCellValue('J'.($i+2),$smsStatus);
			$objectPHPExcel->getActiveSheet()->setCellValue('K'.($i+2),$rs[$i][Identity_id].' ');
			$objectPHPExcel->getActiveSheet()->setCellValue('L'.($i+2),$rs[$i][birthday]);
			$objectPHPExcel->getActiveSheet()->setCellValue('M'.($i+2),$rs[$i][full_street_address]);
			$objectPHPExcel->getActiveSheet()->setCellValue('N'.($i+2),$rs[$i][suburb]);
			$objectPHPExcel->getActiveSheet()->setCellValue('O'.($i+2),$rs[$i][state]);
			$objectPHPExcel->getActiveSheet()->setCellValue('P'.($i+2),$idType);
			$objectPHPExcel->getActiveSheet()->setCellValue('Q'.($i+2),$rs[$i][id_number]);
			$objectPHPExcel->getActiveSheet()->setCellValue('R'.($i+2),$rs[$i][IP]);
				
		}
	
		ob_end_clean();
		ob_start();
			
		header('Content-Type : application/vnd.ms-excel');
		header('Content-Disposition:attachment;filename="'.'Survey User Lists-'.date("j,m,Y").'.xls"');
		$objWriter= PHPExcel_IOFactory::createWriter($objectPHPExcel,'Excel5');
		$objWriter->save('php://output');
	}
	
	/**
	 * 通过活动代码,得到活动名称
	 */
	public function getActivityName($activityCode)
	{
		$rs=Activity::model()->find('activity_code=:activity_code',array(':activity_code'=>$activityCode));
		return $rs->title;
	}
	
	/**
	 * 替换数字对应的内容
	 */
	public function getReplaceAttribute($winningStatus){
		$attribute="";
		if($winningStatus == 0){
			$attribute="Not winning";
		}else if($winningStatus == 1){
			$attribute="Has been winning";
		}
		return $attribute;
	}
	
	/**
	 *替换短信发送状态 
	 */
	public function getSmsReplaceAttribute($attribute){
		$message="";
		if($attribute == 0){
			$message="Not Sent";
		}elseif ($attribute == 1){
			$message="Sent Successfully";
		}else{
			$message="Send Failed";
		}
		return $message;
	}
	
	/**
	 * 替换ID_TYPE 内容
	 * @param unknown $attribute
	 * @return string
	 */
	public function getTypeReplaceAttribute($attribute){
		$message="";
		if($attribute == 1){
			$message="Driver's License/Learner's Permit";
		}elseif ($attribute == 2){
			$message="Australia Passport";
		}elseif ($attribute == 3){
			$message="International Passport";
		}elseif ($attribute == 4){
			$message="Birth Certificate";
		}elseif ($attribute == 5){
			$message="Tertiary Education";
		}elseif ($attribute == 6){
			$message="Medicare Card";
		}
		return $message;
	}
	
}